﻿
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;



namespace TradeManage
{

    public partial class FinishedproductInOut : Form
    {
        public FinishedproductInOut()
        {
            InitializeComponent();
        }



        public void FinishedproductInOut_Load_1(object sender, EventArgs e)
        {
            
            string selected1 = comboBox1.Text;
            string selected3 = comboBox3.Text;
            string selected4 = comboBox4.Text;
            string textBox4 = this.textBox4.Text;
            String textBox6 = this.textBox6.Text;
            String textBox8 = this.textBox8.Text;
            String textBox3 = this.textBox3.Text;
            String textBox5 = this.textBox5.Text;
            String textBox7 = this.textBox7.Text;
            DateTime dateTime1 = dateTimePicker1.Value;

            /*
            dataGridView1.Rows.Add("GX1689102", "GT2830-3", "层层叠1", 6000, 500, 12, "跟单1号", 6000, 500, 0);
            dataGridView1.Rows.Add("GX1689103", "GT2830-4", "层层叠2", 6000, 500, 12, "跟单2号", 6000, 500, 0);
            dataGridView1.Rows.Add("GX1689104", "GT2830-5", "层层叠3", 6000, 500, 12, "跟单3号", 6000, 500, 0);
            dataGridView1.Rows.Add("GX1689105", "GT2830-6", "层层叠4", 6000, 500, 12, "跟单4号", 6000, 500, 0);
            dataGridView1.Rows.Add("GX1689106", "GT2830-7", "层层叠5", 6000, 500, 12, "跟单5号", 6000, 500, 0);
            dataGridView1.Rows.Add("GX1689107", "GT2830-8", "层层叠6", 6000, 500, 12, "跟单6号", 6000, 500, 0);
            dataGridView1.Rows.Add("GX1689108", "GT2830-9", "层层叠7", 6000, 500, 12, "跟单7号", 6000, 500, 0);
            */

            using (SqlConnection connection = new SqlConnection(@"Data Source=.;Initial Catalog =GXFactoryDB ;Integrated Security = True"))
            {
                string sql = "select * from ProductSearch_tb ";
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
                adapter.Fill(ds, "ProductSearch_tb");
                dataGridView1.DataSource = ds.Tables["ProductSearch_tb"];

                //connection.Close();
            }

            using (SqlConnection connection = new SqlConnection(@"Data Source=.;Initial Catalog =GXFactoryDB ;Integrated Security = True"))
            {
                string sql = "select InNumbers,InBoxes,StockDepart,OutNumbers,OutBoxes,OutBillNo from InOrOut_tb ";
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
                adapter.Fill(ds, "InOrOut_tb");
                dataGridView2.DataSource = ds.Tables["InOrOut_tb"];

                connection.Close();
            }

            
            /*
            //判断数据库是否连接成功
            string connectionString = "Server=toolStripLabelLAPTOP-243TBQ42;Database=GXFactoryDB;User Id=sa;Password=123456;";

            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                connection.Open();
       
                string createTableSql = "CREATE TABLE IF NOT EXISTS [user] ([id] INT PRIMARY KEY, [username] NVARCHAR(50), [email] NVARCHAR(100))";
                SqlCommand createTableCmd = new SqlCommand(createTableSql, connection);
                createTableCmd.ExecuteNonQuery();
         
                string insertUserSql = "select * from InOrOut_tb";
                SqlCommand insertUserCmd = new SqlCommand(insertUserSql,connection);
                insertUserCmd.ExecuteNonQuery();

                MessageBox.Show("User record inserted successfully!");
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
           */


            //构造连接字符串
            //string connectionString = "Server = .; Database = GXFactoryDB; uid = sa; pwd = 123456; Trusted_Connection = False;Encrypt=false;";
            
        }


        private void toolS_delete_Click(object sender, EventArgs e)
        {
            //用trycatch语句来捕获异常
            try
            {
                //删除dataGridView2中选中的行
                dataGridView2.Rows.RemoveAt(dataGridView2.CurrentRow.Index);
                MessageBox.Show("删除成功");
            }
            catch (Exception)
            {
                MessageBox.Show("请选中要删除的行");
            }

        }


        private void toolStripButton1_Click_1(object sender, EventArgs e)
        {
            string selected4 = comboBox4.Text;
            DateTime dateTime1 = dateTimePicker1.Value;
            using (SqlConnection connection = new SqlConnection(@"Data Source=.;Initial Catalog =GXFactoryDB ;Integrated Security = True"))
            {
                string insertUserSql = @"
                INSERT INTO [InOrOut_tb]([InOrOut],[InNumbers],[InBoxes],[StockDepart],[OutNumbers],[OutBoxes],[OutBillNo],[UpdateTime])
                VALUES(@InOrOut,@InNumbers,@InBoxes,@StockDepart,@OutNumbers,@OutBoxes,@OutBillNo,@UpdateTime)";

                SqlCommand insertUserCmd = new SqlCommand(insertUserSql, connection);

                // 以下假设每个textBox和dateTime1是准备好的变量
                insertUserCmd.Parameters.AddWithValue("@InOrOut", selected4);
                insertUserCmd.Parameters.AddWithValue("@InNumbers", textBox4.Text);
                insertUserCmd.Parameters.AddWithValue("@InBoxes", textBox6.Text);
                insertUserCmd.Parameters.AddWithValue("@StockDepart", textBox8.Text);
                insertUserCmd.Parameters.AddWithValue("@OutNumbers", textBox3.Text);
                insertUserCmd.Parameters.AddWithValue("@OutBoxes", textBox5.Text);
                insertUserCmd.Parameters.AddWithValue("@OutBillNo", textBox7.Text);
                insertUserCmd.Parameters.AddWithValue("@UpdateTime", dateTime1);

                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                insertUserCmd.ExecuteNonQuery();


                string sql = "select InNumbers,InBoxes,StockDepart,OutNumbers,OutBoxes,OutBillNo from InOrOut_tb ";
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
                adapter.Fill(ds, "InOrOut_tb");
                dataGridView2.DataSource = ds.Tables["InOrOut_tb"];
                MessageBox.Show("添加成功");



                // 关闭数据库连接（如果使用了 using 块，则不需要手动关闭）
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }



                /*
                //在name为dataGridView2的控件里面添加行数据
                dataGridView2.Rows.Add(selected4, textBox4, textBox6, textBox8, textBox3, textBox5, textBox7, dateTime1);
                */


            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            
            string textBox1 = this.textBox1.Text;
            using (SqlConnection connection = new SqlConnection(@"Data Source=.;Initial Catalog =GXFactoryDB ;Integrated Security = True"))
            {
                try {
                    string sql = "select * from ProductSearch_tb where OrderNo = " + textBox1;
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
                    adapter.Fill(ds, "ProductSearch_tb");
                    dataGridView1.DataSource = ds.Tables["ProductSearch_tb"];
                }catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }


            }
        }

        private void toolStripButton3_Click(object sender, EventArgs e)
        {
            //数据库导出为Excel
            if (dataGridView2.Rows.Count == 0)
            {

                MessageBox.Show("当前无数据可导出!");
            }
            else
            {
                ExportDataToExcel(dataGridView2);
            }
        }

        public void ExportDataToExcel(DataGridView dgv)
        {
            if (dgv.Rows.Count == 0)
            {
                return;
            }
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveFileDialog1.Title = "请选择要导出的位置";
            saveFileDialog1.Filter = "Excel文件| *.xlsx;*.xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {

                if (saveFileDialog1.FileName != "")
                {
                    Microsoft.Office.Interop.Excel.Application excel =
                   new Microsoft.Office.Interop.Excel.Application();//Office引用
                    if (excel == null)
                    {
                        MessageBox.Show("创建Excel对象失败，可能您的电脑尚未安装Excel");
                        return;
                    }
                    Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook workbook =
                    workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//？？
                    Microsoft.Office.Interop.Excel.Worksheet worksheet =
                    (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                    int colIndex = 0;
                    for (int i = 0; i < dgv.ColumnCount; i++)
                    {
                        if (dgv.Columns[i].Visible)//用作于不导出隐藏列
                        {
                            colIndex++;
                            worksheet.Cells[1, colIndex] = dgv.Columns[i].HeaderText;
                            //worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
                        }
                    }
                    for (int r = 0; r < dgv.Rows.Count; r++)
                    {
                        colIndex = 0;
                        for (int i = 0; i < dgv.ColumnCount; i++)
                        {
                            if (dgv.Columns[i].Visible)
                            {
                                colIndex++;
                                worksheet.Cells[r + 2, colIndex] = "'" + dgv.Rows[r].Cells[i].Value;
                            }
                        }
                    }
                    System.Windows.Forms.Application.DoEvents();
                    workbook.SaveAs(saveFileDialog1.FileName);
                    excel.Quit();
                    GC.Collect();
                    MessageBox.Show("导出成功！");

                }
            }
        }


        private void toolStripButton4_Click(object sender, EventArgs e)
        {
            //数据库导出为Excel
            if (dataGridView2.Rows.Count == 0)
            {

                MessageBox.Show("当前无数据可导出!");
            }
            else
            {
                ExportDataToExcel(dataGridView1);
            }
        }

        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            //如果选中上面的某行就将上面的数据分别显示在comboBox2，comboBox3，textBox9,textBox10,textBox12,textBox13,textBox14,textBox15,textBox16中
            //获取dataGridView1中的数据
            if (dataGridView1.SelectedRows.Count > 0) // 确保有选中的行
            {
                DataGridViewRow row = dataGridView1.SelectedRows[0]; // 获取当前选中的行

                // 将数据分发到各控件
                comboBox2.Text = row.Cells[0].Value.ToString();
                comboBox3.Text = row.Cells[1].Value.ToString();
                textBox9.Text = row.Cells[2].Value.ToString();
                textBox10.Text = row.Cells[3].Value.ToString();
                textBox12.Text = row.Cells[4].Value.ToString();
                textBox13.Text = row.Cells[5].Value.ToString();
                textBox14.Text = row.Cells[6].Value.ToString();
                textBox15.Text = row.Cells[7].Value.ToString();
                textBox16.Text = row.Cells[8].Value.ToString();
                //dateTimePicker5.Text = row.Cells[9].Value.ToString();
            }
        }

        private void textBox8_TextChanged(object sender, EventArgs e)
        {

        }
    }
}
